package com.devshephard.pdf;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

public class TestExcel1 {
    public static void main(String[] args) {
        try {
            //创建工作簿对象
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream("D:\\zzsFolder\\0zzs.xlsx"));
            //获取工作簿下sheet的个数
            int sheetNum = xssfWorkbook.getNumberOfSheets();
            System.out.println("该excel文件中总共有："+sheetNum+"个sheet");

            /*
             *  对空白的excel进行占位填充,填充有效的矩阵
             */

            //读取第i个工作表 TODO 目前暂定每个excel只有一个sheet
            System.out.println("读取第"+(1)+"个sheet");
            XSSFSheet sheet = xssfWorkbook.getSheetAt(0);

            int maxRow = sheet.getLastRowNum();
            int maxCol = 8; //TODO 目前先写死,但不排除会有上下页分开的情况

            System.out.println("总行长为：" + maxRow);
            System.out.println("总列长为：" + maxRow);


            for (int i = 0; i < maxRow; i++) {
                for (int j = 0; j < maxCol; j++) {
                    XSSFCell cell = sheet.getRow(i).getCell(j);
                    System.out.println(cell.getStringCellValue() != null ? cell.getStringCellValue().replace("\n","") : "");

                    //向右寻找空白列长
                    int blankCol = 0;
                    for (int k = j; k < maxCol; k++) {
                          XSSFCell nextColCell = sheet.getRow(i).getCell(k + 1);
                          if(nextColCell != null){
                              if(nextColCell.getStringCellValue() != null && !"".equals(nextColCell.getStringCellValue())){
                                  break;
                              }else{
                                  blankCol ++;
                              }
                          }else{
                              break;
                          }

                    }

                    //向下寻找空白行长
                    int blankRow = 0;
                    for (int l = i; l < maxRow; l++) {
                        XSSFCell nextRowCell = sheet.getRow(l + 1).getCell(j);
                        if(nextRowCell != null){
                            if( nextRowCell.getStringCellValue() != null && !"".equals(nextRowCell.getStringCellValue())){
                                break;
                            }else{
                                blankRow ++;
                            }
                        }else {
                            break;
                        }

                    }


                    if(blankCol == 0 && blankRow == 0){
                        continue;
                    }

                    //横向填充
                    if(blankCol != 0 ){
                        //进行所属有效表格区间的填充
                        int curColPointer = j;

                        for (int i2 = 0; i2 <= blankCol; i2++) {
                            XSSFCell colCellVal = sheet.getRow(i).getCell(curColPointer);
                            colCellVal.setCellValue(sheet.getRow(i).getCell(j).getStringCellValue());
                            curColPointer ++ ;
                        }
                        continue;
                    }

                    //进行所属有效表格区间的填充
                  /*  int curColPointer = j;
                    int curRowPointer = i;
                    for (int i1 = 0; i1 <= blankRow; i1++) {
                        for (int i2 = 0; i2 <= blankCol; i2++) {

                            XSSFCell colCellVal = sheet.getRow(curRowPointer).getCell(curColPointer);
                            colCellVal.setCellValue(sheet.getRow(i).getCell(j).getStringCellValue());
                            curColPointer ++ ;
                        }
                        curRowPointer ++ ;
                        curColPointer = j;
                    }
*/
                }
            }


            for (int i = 0; i < maxRow; i++) {
                for (int j = 0; j < maxCol; j++) {
                    XSSFCell cell = sheet.getRow(i).getCell(j);
                    System.out.println(cell.getStringCellValue() != null ? cell.getStringCellValue().replace("\n","") : "");

                    //向右寻找空白列长
                    int blankCol = 0;
                    for (int k = j; k < maxCol; k++) {
                        XSSFCell nextColCell = sheet.getRow(i).getCell(k + 1);
                        if(nextColCell != null){
                            if(nextColCell.getStringCellValue() != null && !"".equals(nextColCell.getStringCellValue())){
                                break;
                            }else{
                                blankCol ++;
                            }
                        }else{
                            break;
                        }

                    }

                    //向下寻找空白行长
                    int blankRow = 0;
                    for (int l = i; l < maxRow; l++) {
                        XSSFCell nextRowCell = sheet.getRow(l + 1).getCell(j);
                        if(nextRowCell != null){
                            if( nextRowCell.getStringCellValue() != null && !"".equals(nextRowCell.getStringCellValue())){
                                break;
                            }else{
                                blankRow ++;
                            }
                        }else {
                            break;
                        }

                    }


                    if(blankCol == 0 && blankRow == 0){
                        continue;
                    }

                    //纵向填充
                    if(blankRow != 0){
                        //进行所属有效表格区间的填充
                        int curRowPointer = i;
                        for (int i1 = 0; i1 <= blankRow; i1++) {
                            XSSFCell colCellVal = sheet.getRow(curRowPointer).getCell(j);
                            colCellVal.setCellValue(sheet.getRow(i).getCell(j).getStringCellValue());
                            curRowPointer ++ ;
                        }
                        continue;
                    }

                    //进行所属有效表格区间的填充
                  /*  int curColPointer = j;
                    int curRowPointer = i;
                    for (int i1 = 0; i1 <= blankRow; i1++) {
                        for (int i2 = 0; i2 <= blankCol; i2++) {

                            XSSFCell colCellVal = sheet.getRow(curRowPointer).getCell(curColPointer);
                            colCellVal.setCellValue(sheet.getRow(i).getCell(j).getStringCellValue());
                            curColPointer ++ ;
                        }
                        curRowPointer ++ ;
                        curColPointer = j;
                    }*/

                }
            }


            //5. 通过输出流将对象下载到磁盘
            FileOutputStream fileOutputStream = new FileOutputStream("D:\\zzsFolder\\0zzs_res.xlsx");
            xssfWorkbook.write(fileOutputStream);
            //刷新输出流
            fileOutputStream.flush();



            //6. 释放资源
            fileOutputStream.close();
            xssfWorkbook.close();



        } catch (IOException e) {
            e.printStackTrace();
        }

    }

}
